package com.swhysc.information.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.QBean;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.swhysc.information.entity.*;
import com.swhysc.information.repository.InformationDataColumnExRepository;
import com.swhysc.information.repository.InformationDataDirExRepository;
import com.swhysc.information.repository.InformationDataTableExRepository;
import com.swhysc.information.service.IInformationDataTableExService;
import com.swhysc.information.vo.InformationDataColumnExcelExVo;
import com.swhysc.information.vo.InformationDataTableExVo;
import com.swhysc.information.vo.InformationDataTableExcelExVo;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.sql.Clob;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;


/**
 * @Author zmq
 * @Date 2022/10/14 9:30
 * @Description
 */
@Service
@Slf4j
public class InformationDataTableExServiceImpl implements IInformationDataTableExService {
    @Autowired
    private InformationDataTableExRepository informationDataTableRepository;

    @Autowired
    private InformationDataDirExRepository informationDataDirRepository;

    @Autowired
    private InformationDataColumnExRepository informationDataColumnRepository;

    @Autowired
    private JPAQueryFactory jpaQueryFactory;


    @Value("${data.security.file.path}")
    private String uploadPath;

    @Override
    public List getList(String queryParam, String dataSourceIds, Integer page, Integer size) {
        //String userId = UserSessionUtil.getEmpId();
        Pageable pageable = PageRequest.of(page - 1, size);
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
        //QEmp qEmp = QEmp.emp;
        QInformationDataDirectoryEx qInformationDataDirectory1 = new QInformationDataDirectoryEx("qInformationDataDirectoryq1");
        QInformationDataDirectoryEx qInformationDataDirectory2 = new QInformationDataDirectoryEx("qInformationDataDirectoryq2");
        //初始化查询条件，类似(where 1 = 1)
        Predicate predicate = qInformationDataTable.isNotNull().or(qInformationDataTable.isNull());
        //查询中文名和英文名
        if (StringUtils.isNotBlank(queryParam)) {
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.enName.likeIgnoreCase("%" + queryParam + "%")
                    .or(qInformationDataTable.chName.likeIgnoreCase("%" + queryParam + "%")));

        }
        //查询数据源
        if (StringUtils.isNotBlank(dataSourceIds)) {
            String[] split = dataSourceIds.split(",");
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.dataSourceId.in(split));
        }
        List<InformationDataTableExVo> voList = jpaQueryFactory.select(qInformationDataTable, qInformationDataDirectory1.name, qInformationDataDirectory2.level)
                .from(qInformationDataTable)
                .leftJoin(qInformationDataDirectory1)
                .on(qInformationDataTable.dataSourceId.eq(qInformationDataDirectory1.id))
                .leftJoin(qInformationDataDirectory2)
                .on(qInformationDataTable.dirId.eq(qInformationDataDirectory2.id))
                .where(predicate)
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .orderBy(qInformationDataTable.updateTime.desc())
                .fetch().stream().map(item -> {
                    InformationDataTableExVo vo = new InformationDataTableExVo();
                    BeanUtils.copyProperties(item.get(0, QInformationDataTableEx.class), vo);
                    vo.setDataSourceName(item.get(qInformationDataDirectory1.name));
                    vo.setLevel(item.get(qInformationDataDirectory2.level));
                    return vo;
                }).collect(Collectors.toList());
        return voList;
    }

    @Override
    public Long getCount(String queryParam, String dataSourceIds) {
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
        //初始化查询条件，类似(where 1 = 1)
        Predicate predicate = qInformationDataTable.isNotNull().or(qInformationDataTable.isNull());
        //查询中文名和英文名
        if (StringUtils.isNotBlank(queryParam)) {
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.enName.likeIgnoreCase("%" + queryParam + "%")
                    .or(qInformationDataTable.chName.likeIgnoreCase("%" + queryParam + "%")));

        }
        //查询数据源
        if (StringUtils.isNotBlank(dataSourceIds)) {
            String[] split = dataSourceIds.split(",");
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.dataSourceId.in(split));
        }
        long count = jpaQueryFactory.selectFrom(qInformationDataTable).where(predicate).fetchCount();
        return count;
    }

    @Override
    @Transactional
    public Boolean create(InformationDataTableEx informationDataTable) {
        //String userId = UserSessionUtil.getEmpId();
        //判断是否叶子目录，非叶子节点不允许创建
        String dirId = informationDataTable.getDirId();
        Assert.isTrue(StringUtils.isNotBlank(dirId), "数据目录不能为空！");
        InformationDataDirectoryEx informationDataDirectory = informationDataDirRepository.getOne(dirId);
        Assert.isTrue("1".equals(informationDataDirectory.getIsLeaf()), "非叶子节点不能创建资讯数据表！");
        //非空校验
        String enName = informationDataTable.getEnName();
        String chName = informationDataTable.getChName();
        Assert.isTrue(StringUtils.isNotBlank(enName), "数据表英文名不能为空！");
        Assert.isTrue(StringUtils.isNotBlank(chName), "数据表中文名不能为空！");
        //校验名称是否重复
        Assert.isTrue(checkName(informationDataTable.getId(), enName, informationDataTable.getDirId()), "资讯数据表名称重复！");
        //保存资讯数据表
        //informationDataTable.setCreateUser(userId);
        //informationDataTable.setUpdateUser(userId);
        informationDataTable.setCreateTime(new Date());
        informationDataTable.setUpdateTime(new Date());
        InformationDataTableEx informationDataTable1 = informationDataTableRepository.save(informationDataTable);
        //保存字段信息
        List<InformationDataColumnEx> informationDataColumnList = informationDataTable.getInformationDataColumnList();
        if (CollectionUtils.isNotEmpty(informationDataColumnList)) {
            for (InformationDataColumnEx informationDataColumn : informationDataColumnList) {
                informationDataColumn.setTableId(informationDataTable1.getId());
            }
            informationDataColumnRepository.saveAll(informationDataColumnList);
        }
        return informationDataTable1 != null;
    }

    /**
     * 名称重复校验
     *
     * @param id
     * @param enName
     * @param dirId
     * @return
     */
    private boolean checkName(String id, String enName, String dirId) {
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
        Predicate predicate = qInformationDataTable.dirId.eq(dirId).and(qInformationDataTable.enName.eq(enName));
        if (StringUtils.isNotBlank(id)) {
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.id.notEqualsIgnoreCase(id));
        }
        long count = jpaQueryFactory.selectFrom(qInformationDataTable).where(predicate).fetchCount();
        return count == 0;
    }

    @Override
    public Boolean update(InformationDataTableEx informationDataTable) {
        String id = informationDataTable.getId();
        //id校验
        Assert.isTrue(StringUtils.isNotBlank(id), "数据表Id不能为空!");
        //非空校验
        String enName = informationDataTable.getEnName();
        String chName = informationDataTable.getChName();
        Assert.isTrue(StringUtils.isNotBlank(enName), "数据表英文名不能为空！");
        Assert.isTrue(StringUtils.isNotBlank(chName), "数据表中文名不能为空！");
        //校验名称是否重复
        Assert.isTrue(checkName(informationDataTable.getId(), enName, informationDataTable.getDirId()), "资讯数据表名称重复！");
        //保存数据表
        InformationDataTableEx save = informationDataTableRepository.save(informationDataTable);
        //保存数据表字段 1、先删除 2、再添加
        List<String> ids = new ArrayList<>();
        ids.add(id);
        int i = informationDataColumnRepository.deleteInformationDataColumnsByTableIdIn(ids);
        List<InformationDataColumnEx> columnList = informationDataTable.getInformationDataColumnList();
        if (CollectionUtils.isNotEmpty(columnList)) {
            informationDataColumnRepository.saveAll(columnList);
        }
        return save != null;
    }

    @Override
    @Transactional
    public Map<String, Object> delete(String ids) {
        Map<String, Object> map = new HashMap<>();
        Assert.isTrue(StringUtils.isNotBlank(ids), "请选中要删除的数据！");
        String[] split = ids.split(",");
        List<String> idList = Arrays.asList(split);
        try {
            //先删除表字段
            informationDataColumnRepository.deleteInformationDataColumnsByTableIdIn(idList);
            //最后删除表
            int i = informationDataTableRepository.deleteInformationDataTablesByIdIn(idList);
            map.put("flag", true);
            map.put("message", "删除成功,删除" + i + "条！");
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            map.put("flag", false);
            map.put("message", "删除失败！");
            return map;
        }
    }

    /**
     * 根据左侧数据源目录树id获取数据表列表
     *
     * @param dirId 目录id
     * @param page  页数
     * @param size  每页个数
     * @return
     */
    @Override
    public List getDirList(String dirId, Integer page, Integer size, String name) {
        Assert.isTrue(StringUtils.isNotBlank(dirId), "目录id不能为空");
        //根据数据源目录id获取 下边所有的叶子节点
        List<String> dirIds = getDirIdList(dirId);
        if (CollUtil.isEmpty(dirIds)) {
            return new ArrayList();
        }
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;

        QInformationDataDirectoryEx qInformationDataDirectory = QInformationDataDirectoryEx.informationDataDirectoryEx;

        //初始化查询条件
        Predicate predicate = qInformationDataTable.dirId.in(dirIds);
        if (StringUtils.isNotBlank(name)) {
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.enName.likeIgnoreCase("%" + name + "%").or(qInformationDataTable.chName.likeIgnoreCase("%" + name + "%")));
        }
        JPAQuery<Tuple> tupleJPAQuery = jpaQueryFactory.select(qInformationDataTable, qInformationDataDirectory.name)
                .from(qInformationDataTable)
                .leftJoin(qInformationDataDirectory)
                .on(qInformationDataTable.dirId.eq(qInformationDataDirectory.id))
                .where(predicate)
                .orderBy(qInformationDataTable.sort.asc(), qInformationDataTable.updateTime.desc(), qInformationDataTable.enName.asc());
        //是否分页
        if (null != page && null != size){
            Pageable pageable = PageRequest.of(page - 1, size);
            tupleJPAQuery = tupleJPAQuery.offset(pageable.getOffset())
                    .limit(pageable.getPageSize());
        }
        List<InformationDataTableExVo> collect = tupleJPAQuery.fetch().stream().map(item -> {
            InformationDataTableExVo vo = new InformationDataTableExVo();
            BeanUtils.copyProperties(item.get(qInformationDataTable), vo);
            vo.setDirName(item.get(qInformationDataDirectory.name));
            return vo;
        }).collect(Collectors.toList());
        return collect;
    }

    /**
     * 根据数据源目录id获取 下边所有的叶子节点
     *
     * @param dirId
     * @return List
     */
    public List<String> getDirIdList(String dirId) {
        //根据id获取到levelcode，模糊查询levelcode的全部数据 (isleaf=1 )，最终获取最后节点的id集合
        InformationDataDirectoryEx dirRepositoryOne = informationDataDirRepository.getOne(dirId);
        if (ObjectUtil.isEmpty(dirRepositoryOne)) {
            return Lists.newArrayList();
        }
        String levelCode = dirRepositoryOne.getLevelCode();
        QInformationDataDirectoryEx qInfoDir = QInformationDataDirectoryEx.informationDataDirectoryEx;
        Predicate predicate = qInfoDir.isNotNull().or(qInfoDir.isNull());
        //叶子节点 + 目录类型
        predicate = ExpressionUtils.and(predicate, qInfoDir.isLeaf.eq("1"));
        predicate = ExpressionUtils.and(predicate, qInfoDir.type.eq(2));
        predicate = ExpressionUtils.and(predicate, qInfoDir.levelCode.startsWith(levelCode.trim()));

        List<InformationDataDirectoryEx> directoryList = jpaQueryFactory.selectFrom(qInfoDir).where(predicate).fetch();
        log.info("【根据数据源目录id获取叶子节点 {} 个】", directoryList.size());
        return directoryList.stream().map(InformationDataDirectoryEx::getId).collect(Collectors.toList());
    }

    @Override
    public Long getDirCount(String dirId, String name) {
        Assert.isTrue(StringUtils.isNotBlank(dirId), "目录id不能为空");
        List<String> dirIdList = getDirIdList(dirId);
        System.out.println(dirIdList);
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
        //初始化查询条件
        Predicate predicate = qInformationDataTable.dirId.in(dirIdList);
        if (StringUtils.isNotBlank(name)) {
            predicate = ExpressionUtils.and(predicate, qInformationDataTable.enName.likeIgnoreCase("%" + name + "%").or(qInformationDataTable.chName.likeIgnoreCase("%" + name + "%")));
        }
        long count = jpaQueryFactory.selectFrom(qInformationDataTable)
                .where(predicate)
                .fetchCount();
        return count;
    }

    @Override
    public InformationDataTableExVo getOne(String id) {
        InformationDataTableExVo vo = new InformationDataTableExVo();
        Assert.isTrue(StringUtils.isNotBlank(id), "数据表id不能为空！");
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
        QInformationDataColumnEx qInformationDataColumn = QInformationDataColumnEx.informationDataColumnEx;
        QInformationDataDirectoryEx qInformationDataDirectory = QInformationDataDirectoryEx.informationDataDirectoryEx;
        Tuple tuple = jpaQueryFactory.select(qInformationDataTable, qInformationDataDirectory.name)
                .from(qInformationDataTable)
                .leftJoin(qInformationDataDirectory)
                .on(qInformationDataTable.dataSourceId.eq(qInformationDataDirectory.id))
                .where(qInformationDataTable.id.eq(id))
                .fetchOne();
        if (null == tuple) {
            return vo;
        }
        BeanUtils.copyProperties(tuple.get(qInformationDataTable), vo);
        vo.setDataSourceName(tuple.get(qInformationDataDirectory.name));
        List<InformationDataColumnEx> list = jpaQueryFactory.selectFrom(qInformationDataColumn).where(qInformationDataColumn.tableId.eq(id)).fetch();
        vo.setInformationDataColumnList(list);
        return vo;
    }


    @Override
    public Map<String, Object> importData(HttpServletRequest request, HttpServletResponse response, MultipartFile multipartFile) throws Exception {
        Map<String, Object> result = Maps.newLinkedHashMap();
        AtomicInteger tableSuccess = new AtomicInteger(0);
        AtomicInteger tableFailure = new AtomicInteger(0);
        AtomicInteger colmunSuccess = new AtomicInteger(0);
        AtomicInteger colmunFailure = new AtomicInteger(0);

        //校验文件
        if (multipartFile != null && multipartFile.getOriginalFilename() != null &&
                (".xlsx".equals(multipartFile.getOriginalFilename().substring(multipartFile.getOriginalFilename().lastIndexOf(".")))
                        ||".xls".equals(multipartFile.getOriginalFilename().substring(multipartFile.getOriginalFilename().lastIndexOf("."))))
        ) {
            File file = new File(uploadPath);
            if (!file.exists()) {
                file.mkdir();
            }
            //上传文件
            String xlsPath = uploadFile(multipartFile, uploadPath);
            //开始导入
            readExcel(xlsPath, tableSuccess, tableFailure, colmunSuccess, colmunFailure);
        } else {
            result.put("message", "导入文件格式有误，请检查！");
        }
        result.put("tableSuccess", tableSuccess + "");
        result.put("tableFailure", tableFailure + "");
        result.put("columuSuccess", colmunSuccess + "");
        result.put("colmunFailure", colmunFailure + "");
        return result;
    }

    public void readExcel(String xlsPath, AtomicInteger tableSuccess, AtomicInteger tableFailure, AtomicInteger colmunSuccess, AtomicInteger colmunFailure) throws Exception {
        //获取文件
        InputStream in = new FileInputStream(xlsPath);
        Workbook workbook = WorkbookFactory.create(in);
        if (null == workbook) {
            log.info("文件读取失败！！");
            throw new RuntimeException("文件读取失败");
        }
        log.info("开始导入资讯数据表！！！！！！！！！！！！！！！");
        //1、先导入表
        //获取第一个sheet页
        Sheet tableSheet = workbook.getSheetAt(0);
        if (null == tableSheet) {
            log.info("不存在表目录sheet页");
            throw new RuntimeException("不存在表目录sheet页");
        }
        int tableLastRowNum = tableSheet.getLastRowNum();
        if (0 == tableLastRowNum) {
            log.info("表目录sheet页无数据");
            throw new RuntimeException("表目录sheet页无数据");
        }
        List<InformationDataTableEx> tableList = new ArrayList<>();
        Map<String, List<String>> dataSourceIdTableNames = new HashMap<>();
        QInformationDataDirectoryEx qInformationDataDirectory = QInformationDataDirectoryEx.informationDataDirectoryEx;
        for (int i = 1; i <= tableLastRowNum; i++) {
            InformationDataTableEx informationDataTable = new InformationDataTableEx();
            //获取行，判断是否为空
            Row row = tableSheet.getRow(i);
            if (null == row) {
                continue;
            }
            //(1)设置表英文名
            String tableEnName = getCell(row.getCell(1));
            if (StringUtils.isBlank(tableEnName)) {
                log.info("第[{}]行数据表名不存在", i + 1);
                tableFailure.incrementAndGet();
                continue;
            } else {
                informationDataTable.setEnName(tableEnName);
            }
            //(2)设置表中文名
            String tableChName = getCell(row.getCell(2));
            if (StringUtils.isBlank(tableChName)) {
                log.info("第[{}]行表[{}]中文名不存在", i + 1, tableChName);
            } else {
                informationDataTable.setChName(tableChName);
            }
            //(3) 设置关联目录id
            String dirName;
            String dirName1;
            //获取三级目录，三级目录为空获取二级目录
            String third = getCell(row.getCell(5));
            if (StringUtils.isBlank(third)) {
                String second = getCell(row.getCell(4));
                if (StringUtils.isBlank(second)) {
                    String first = getCell(row.getCell(3));
                    if (StringUtils.isBlank(first)) {
                        log.info("表[{}]没有关联目录", tableEnName);
                        tableFailure.incrementAndGet();
                        continue;
                    } else {
                        dirName = first;
                        dirName1 = getCell(row.getCell(0));
                    }
                } else {
                    dirName = second;
                    dirName1 = getCell(row.getCell(3));
                }
            } else {
                dirName = third;
                dirName1 = getCell(row.getCell(4));
            }
            //根据目录名字获取关联目录id
            List<InformationDataDirectoryEx> informationDataDirectorysByNameList = informationDataDirRepository.findInformationDataDirectorysByName(dirName);
            if (CollectionUtils.isEmpty(informationDataDirectorysByNameList)) {
                log.info("目录[{}]不存在", dirName);
                tableFailure.incrementAndGet();
                continue;
            } else if (informationDataDirectorysByNameList.size() == 1) { //只有一条，就是这个目录
                InformationDataDirectoryEx informationDataDirectory = informationDataDirectorysByNameList.get(0);
                isLeaf(informationDataDirectory,informationDataTable,qInformationDataDirectory);
            } else if (informationDataDirectorysByNameList.size() > 1) { //多条数据，进一步往上定位找到这个目录id
                for (InformationDataDirectoryEx informationDataDirectory : informationDataDirectorysByNameList) {
                    String parentId = informationDataDirectory.getParentId();
                    Optional<InformationDataDirectoryEx> optional = informationDataDirRepository.findById(parentId);
                    if (optional.isPresent()){
                        InformationDataDirectoryEx one = optional.get();
                        if (one.getName().equals(dirName1)) { //找到了当前这个目录
                            //判断当前这个目录是否是叶子目录，不是则移动到一个下级的叶子目录
                            isLeaf(informationDataDirectory,informationDataTable,qInformationDataDirectory);
                        }
                    }
                }
            }
            if (StringUtils.isBlank(informationDataTable.getDirId())) {
                log.info("目录[{}]不存在", dirName);
                tableFailure.incrementAndGet();
                continue;
            }

            //(4) 设置关联数据源id
            String dataSourceName = getCell(row.getCell(0));
            if (StringUtils.isBlank(dataSourceName)) {
                log.info("表[{}]没有关联数据源", tableEnName);
                tableFailure.incrementAndGet();
                continue;
            } else {
                InformationDataDirectoryEx informationDataDirectory1 = informationDataDirRepository.findInformationDataDirectoryByName(dataSourceName);
                if (null == informationDataDirectory1 || StringUtils.isBlank(informationDataDirectory1.getId())) {
                    log.info("数据源[{}]不存在", dirName);
                    tableFailure.incrementAndGet();
                    continue;
                } else {
                    String dataSourceId = informationDataDirectory1.getId();
                    informationDataTable.setDataSourceId(dataSourceId);

                    List<String> strings = dataSourceIdTableNames.get(dataSourceId);
                    if (CollectionUtils.isNotEmpty(strings)) {
                        strings.add(tableEnName);
                        dataSourceIdTableNames.put(dataSourceId, strings);
                    } else {
                        List<String> strings1 = new ArrayList<>();
                        strings1.add(tableEnName);
                        dataSourceIdTableNames.put(dataSourceId, strings1);
                    }
                }
            }
            //(5) 设置表内容说明
            String tableContent = getCell(row.getCell(7)).replaceAll("\n", "<br>");
            informationDataTable.setContent(tableContent);
            //(6) 设置采集频率
            informationDataTable.setFrequency(getCell(row.getCell(6)));
            //(7) 设置创建时间和修改时间
            informationDataTable.setCreateTime(new Date());
            informationDataTable.setUpdateTime(new Date());

            informationDataTable.setSort(1);
            //(8) 设置采购状态字段
            informationDataTable.setPurchasStatus(getCell(row.getCell(8)));
            tableList.add(informationDataTable);
            tableSuccess.incrementAndGet();
        }

        //修改表 获取全部
        List<InformationDataTableEx> allTable = informationDataTableRepository.findAll();
        for (InformationDataTableEx informationDataTable : tableList) {
            String enName = informationDataTable.getEnName();
            String dataSourceId = informationDataTable.getDataSourceId();
            for (InformationDataTableEx dataTable : allTable) {
                if (enName.equals(dataTable.getEnName()) && dataSourceId.equals(dataTable.getDataSourceId())) {
                    informationDataTable.setId(dataTable.getId());
                    break;
                }
            }
        }

        //全部插入表
        List<InformationDataTableEx> informationDataTableList = informationDataTableRepository.saveAll(tableList);

        if (CollectionUtils.isEmpty(informationDataTableList)) {
            throw new RuntimeException("资讯数据表导入失败");
        }
        log.info("资讯数据表导入完成，共导入[{}]条", informationDataTableList.size());

        //2、再导入字段 从第2个sheet页开始
        int sheets = workbook.getNumberOfSheets();
        long colCount = 0;
        for (int i = 1; i < sheets; i++) {
            //获取sheet页，根据名字判断
            Sheet sheetAt = workbook.getSheetAt(i);
            if (sheetAt == null) {
                log.info("第[{}]个sheet页为空", i + 1);
                continue;
            }
            //以sheet页名字去查询
            String sheetName = sheetAt.getSheetName();
            String sheetName1 = sheetName.replaceAll("字典", "");
            String sheetName2 = sheetName.replaceAll("库字典", "");
            List<InformationDataDirectoryEx> informationDataDirectoryList = jpaQueryFactory.select(qInformationDataDirectory)
                    .from(qInformationDataDirectory)
                    .where(qInformationDataDirectory.name.like("%" + sheetName1 + "%").or(qInformationDataDirectory.name.like("%" + sheetName2 + "%")))
                    .fetch();
            String dataSourceId = "";
            for (InformationDataDirectoryEx informationDataDirectory : informationDataDirectoryList) {
                if (1 == informationDataDirectory.getType()) {
                    dataSourceId = informationDataDirectory.getId();
                }
            }
            if (StringUtils.isBlank(dataSourceId)) {
                log.info("sheet页[{}]没有匹配出数据库", sheetName);
                continue;
            }
            //获取每一行
            int lastRowNum = sheetAt.getLastRowNum();
            Set<String> tableIdlist = new LinkedHashSet<>();
            List<InformationDataColumnEx> informationDataColumns = new ArrayList<>();
            for (int j = 1; j <= lastRowNum; j++) {
                InformationDataColumnEx informationDataColumn = new InformationDataColumnEx();
                Row row = sheetAt.getRow(j);
                if (null == row) {
                    log.info("sheet页[{}],第[{}]行为空", sheetName, j + 1);
                    continue;
                }

                //取出表名
                String tableName = getCell(row.getCell(0));
                if (StringUtils.isBlank(tableName)) {
                    log.info("sheet页[{}]第[{}]行没有表名", sheetName, j + 1);
                    colmunFailure.incrementAndGet();
                    continue;
                }
                QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
                String tableId = jpaQueryFactory.select(qInformationDataTable.id)
                        .from(qInformationDataTable)
                        .where(qInformationDataTable.enName.equalsIgnoreCase(tableName).and(qInformationDataTable.dataSourceId.eq(dataSourceId)))
                        .fetchFirst();
                if (StringUtils.isBlank(tableId)) {
                    log.info("sheet页[{}]第[{}]行的表名未找到", sheetName, j + 1);
                    colmunFailure.incrementAndGet();
                    continue;
                }
                informationDataColumn.setTableId(tableId);
                tableIdlist.add(tableId);
                //设置字段信息
                String colEnName = getCell(row.getCell(1));
                if (StringUtils.isBlank(colEnName)) {
                    log.info("sheet页[{}]第[{}]行没有字段名", sheetName, j + 1);
                    colmunFailure.incrementAndGet();
                    continue;
                }
                informationDataColumn.setEnName(colEnName);
                informationDataColumn.setChName(getCell(row.getCell(2)));
                informationDataColumn.setDataSourceId(dataSourceId);
                String colType = getCell(row.getCell(3));
                if (StringUtils.isBlank(colType)) {
                    log.info("sheet页[{}]第[{}]行字段类型缺失", sheetName, j + 1);
                }
                informationDataColumn.setColType(colType);
                String isNull = getCell(row.getCell(4));
                String isKey = getCell(row.getCell(5));
                String remark = getCell(row.getCell(6));
                String remark1 = remark.replaceAll("\n", "<br>");
                informationDataColumn.setRemark(remark1);
                if ("是".equals(isNull)) {
                    informationDataColumn.setIsNull("1");
                } else if ("否".equals(isNull)) {
                    informationDataColumn.setIsNull("0");
                }
                if ("是".equals(isKey)) {
                    informationDataColumn.setIsKey("1");
                } else if ("否".equals(isKey)) {
                    informationDataColumn.setIsKey("0");
                }
                informationDataColumn.setCreateTime(new Date());
                informationDataColumn.setUpdateTime(new Date());

                informationDataColumns.add(informationDataColumn);
                colmunSuccess.incrementAndGet();
            }
            //已有表字段的删除
            //tableIdlist 进行拆分 使长度 最大为 1000
            List lists = new ArrayList<>();
            lists.addAll(tableIdlist);
            if (lists.size() > 1000) {
                int size = lists.size();
                for (int j = 0; j < lists.size(); j++) {
                    if ((j + 1) % 1000 == 0) {
                        List list = lists.subList(j - 999, j + 1);
                        int z = informationDataColumnRepository.deleteInformationDataColumnsByTableIdIn(list);
                    }
                }
            } else {
                int z = informationDataColumnRepository.deleteInformationDataColumnsByTableIdIn(tableIdlist);
            }
            //全部插入
            List<InformationDataColumnEx> list = informationDataColumnRepository.saveAll(informationDataColumns);
            if (CollectionUtils.isEmpty(list)) {
                log.info("sheet页[{}]导入完成,共导入0条数据", sheetName);
            }
            log.info("sheet页[{}]导入完成,共导入[{}]条数据", sheetName, list.size());
            colCount += list.size();
        }
        log.info("字段导入完成,共导入[{}]条数据！！", colCount);
    }

    public void isLeaf(InformationDataDirectoryEx informationDataDirectory, InformationDataTableEx informationDataTable, QInformationDataDirectoryEx qInformationDataDirectory){
        //判断当前这个目录是否是叶子目录，不是则移动到一个下级的叶子目录
        if ("1".equals(informationDataDirectory.getIsLeaf())){
            informationDataTable.setDirId(informationDataDirectory.getId());
        }else {
            //找到下级
            List<InformationDataDirectoryEx> list = jpaQueryFactory.selectFrom(qInformationDataDirectory)
                    .where(qInformationDataDirectory.parentId.eq(informationDataDirectory.getId()))
                    .fetch();
            //看是否有叶子目录
            List<String> ids = new ArrayList<>();
            boolean flag = false;
            String dirId = "";
            for (InformationDataDirectoryEx dataDirectory : list) {
                ids.add(dataDirectory.getId());
                if ("1".equals(dataDirectory.getIsLeaf())){
                    flag = true;
                    dirId = dataDirectory.getId();
                    break;
                }
            }
            if (flag){
                informationDataTable.setDirId(dirId);
            }else { //没有叶子目录，继续找下级
                List<InformationDataDirectoryEx> fetch = jpaQueryFactory.selectFrom(qInformationDataDirectory)
                        .where(qInformationDataDirectory.parentId.in(ids))
                        .fetch();
                for (InformationDataDirectoryEx dataDirectory : fetch) {
                    if ("1".equals(dataDirectory.getIsLeaf())){
                        informationDataTable.setDirId(dataDirectory.getId());
                        break;
                    }
                }
            }
        }
    }


    /**
     * 截取set集合
     *
     * @param objSet
     * @param size
     * @return
     */
    public Set<Object> subSet(Set<Object> objSet, int size) {
        if (CollectionUtils.isEmpty(objSet)) {
            return Collections.emptySet();
        }
        return ImmutableSet.copyOf(Iterables.limit(objSet, size));
    }


    /****
     * 格式化处理单元格内容
     * @param cell
     * @return
     */
    public static String getCell(Cell cell) {
        if (cell != null) {
            String cellValue = "";
            switch (cell.getCellType()) {
                case NUMERIC:
                    /*cellValue = String.valueOf(cell.getNumericCellValue());
                    break;*/

                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                        SimpleDateFormat sdf = null;
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                                .getBuiltinFormat("h:mm")) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        cellValue = sdf.format(date);
                    } else if (cell.getCellStyle().getDataFormat() == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {
                        cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                    }
                    break;


                case STRING:
                    cellValue = cell.getStringCellValue().trim();
                    break;
                case BLANK:
                    cellValue = "";
                    break;
            }
            return cellValue;
        } else {
            return "";
        }
    }

    /**
     * 上传文件
     *
     * @param file
     * @param uploadPath
     * @return
     */
    private String uploadFile(MultipartFile file, String uploadPath) {
        String returnPath = "";
        if (file != null) {
            returnPath = uploadPath + "/" + file.getOriginalFilename();
            try {
                file.transferTo(new File(returnPath));
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return returnPath;
    }

    /**
     * 导出数据表和数据表的字段信息
     *
     * @param id       数据表id
     * @param response 响应体
     */
    @Override
    public void export(String id, HttpServletResponse response) {
        Assert.isTrue(StrUtil.isNotBlank(id), "数据表id参数不能为空");
        //查询数据表的数据list 数据表+目录表关联 库名	库表英文名 库表中文名 厂商维度一级 厂商维度二级	厂商维度三级	采集频率	说明
        InformationDataTableEx tableObj = informationDataTableRepository.getOne(id);
        List<InformationDataTableExcelExVo> tableList = getTableList(tableObj);
        //查询数据表对应的字段信息list  表名	列名	中文名称	类型	空否	是否为主键	备注
        List<InformationDataColumnExcelExVo> clomnList = getClomnList(id);
        exportExcel(tableList, response, clomnList);
    }



    /**
     * 批量导出
     * @param id 目录id
     * @param isAll 是否全部导出 1-是 0-否
     * @param response
     */
    @SneakyThrows
    @Override
    public void AllExport(String id, String isAll, HttpServletResponse response) {
        Assert.isTrue(StrUtil.isNotBlank(isAll), "是否全部导出参数不能为空");
        //根据选中制定目录id进行导出
        if (StrUtil.isNotBlank(id) && ObjectUtil.equal("0", isAll)) {
            InformationDataDirectoryEx dirRepositoryOne = informationDataDirRepository.getOne(id);
            if (dirRepositoryOne == null) {
                log.info("【没有查询到当前目录信息,id ={}】", id);
                throw new RuntimeException("没有查询到当前目录信息");
            }
            //线程callable获取数据
            List  list = getListThread(dirRepositoryOne, new HashSet<>());
            List<InformationDataTableExcelExVo> directoryList = (List<InformationDataTableExcelExVo>) list.get(0);
            List<InformationDataColumnExcelExVo> clomnList = (List<InformationDataColumnExcelExVo>) list.get(1);
            //导出
            exportExcelTable(response, isAll, directoryList, clomnList, new ArrayList<>(),dirRepositoryOne.getName());
        }
        if (StrUtil.isBlank(id) && ObjectUtil.equal("1", isAll)) {
            //导出全部数据源对应的目录和表、字段表数据
            QInformationDataDirectoryEx qInfoDir = QInformationDataDirectoryEx.informationDataDirectoryEx;
            Predicate predicate = qInfoDir.isNotNull().or(qInfoDir.isNull());
            predicate = ExpressionUtils.and(predicate, qInfoDir.parentId.eq("-1"));
            //获取所有的数据源目录数据
            List<InformationDataDirectoryEx> directoryList = jpaQueryFactory
                    .select(qInfoDir)
                    .from(qInfoDir)
                    .where(predicate)
                    .fetch();
            Set<String> dirIds = directoryList.stream().map(InformationDataDirectoryEx::getId).collect(Collectors.toSet());
            //获取数据
            List list = getListThread(new InformationDataDirectoryEx(), dirIds);
            List<InformationDataTableExcelExVo> tableAndDirByDirIds = (List<InformationDataTableExcelExVo>) list.get(0);
            log.info("【批量导出目录表数量 ={}】",tableAndDirByDirIds.size());
            List<InformationDataColumnExcelExVo> clomnList = (List<InformationDataColumnExcelExVo>) list.get(1);
            log.info("【批量导出目字段表数量 ={}】",clomnList.size());
            exportExcelTable(response, isAll, tableAndDirByDirIds, clomnList, directoryList,"数据表字段信息");
        }

    }

    /**
     * 使用callable获取数据
     * @param dirRepositoryOne
     * @return
     * @throws InterruptedException
     * @throws ExecutionException
     */
    private List getListThread(InformationDataDirectoryEx dirRepositoryOne, Set<String> stringSet) throws Exception{
        List<Future> futureList = new ArrayList<>();
        ExecutorService executorService = Executors.newFixedThreadPool(4);
        //添加任务
        Callable<List> task = new ExportThreadCallableEx(dirRepositoryOne, stringSet, this);
        Future future = executorService.submit(task);
        //接受返回结果
        futureList.add(future);
        List list = (List) futureList.get(0).get();
        executorService.shutdown();
        return list;
    }

    /**
     * 获取excel导出目录sheet页的数据
     * @param dirRepositoryOne 目录实体
     * @param dirIds 目录id集合
     * @return
     */
    public List<InformationDataTableExcelExVo> getInformationDataTableExcelVos(InformationDataDirectoryEx dirRepositoryOne, Set<String> dirIds) {
        QInformationDataTableEx qTable = QInformationDataTableEx.informationDataTableEx;
        QInformationDataDirectoryEx qInfoDir = new QInformationDataDirectoryEx("qInfoDir");
        QInformationDataDirectoryEx qInfoSourceDir = new QInformationDataDirectoryEx("qInfoSourceDir");
        //Predicate predicate = qTable.id.eq(tableObj.getId());
        Predicate predicate = qInfoDir.isNotNull().or(qInfoDir.isNull());
        String levelCode = dirRepositoryOne.getLevelCode();
        if (StrUtil.isNotBlank(levelCode)){
            predicate = ExpressionUtils.and(predicate, qInfoDir.levelCode.startsWith(levelCode));
        }
        if (dirIds != null && dirIds.size() > 0 ){
            predicate = ExpressionUtils.and(predicate, qInfoSourceDir.id.in(dirIds));
        }
        List<InformationDataTableExcelExVo> directoryList = jpaQueryFactory.select(buildQueryBean(qTable, qInfoDir, qInfoSourceDir))
                .from(qTable)
                .innerJoin(qInfoDir).on(qTable.dirId.eq(qInfoDir.id))
                .innerJoin(qInfoSourceDir).on(qTable.dataSourceId.eq(qInfoSourceDir.id))
                .where(predicate)
                .fetch();
        return directoryList;
    }

    @Async("taskExecutor")
    public  void exportExcelTable(HttpServletResponse response, String isAll, List<InformationDataTableExcelExVo> tableExcelVoList, List<InformationDataColumnExcelExVo> clomnList, List<InformationDataDirectoryEx> directoryList, String fileName) {
        ExcelWriter writer = ExcelUtil.getWriter();
        try {
            writer.renameSheet("目录");
            //自定义标题别名
            Map<String, String> headerAliasP = getOneSheetHeaderName();
            writer.setHeaderAlias(headerAliasP);
            writer.setOnlyAlias(true);
            writer.write(tableExcelVoList, true);
            //设置所有列为自动宽度，不考虑合并单元格
            writer.autoSizeColumnAll();
            if ("0".equals(isAll)){
                writer.setSheet(tableExcelVoList.get(0).getDataSourceName() + "字典");
                //自定义标题别名
                writer.setHeaderAlias(getOtherSheetHeaderName());
                writer.setOnlyAlias(true);
                writer.write(clomnList, true);
                writer.autoSizeColumnAll();
            }else if("1".equals(isAll)){
                for (InformationDataDirectoryEx directory : directoryList) {
                    writer.setSheet(directory.getName() + "字典");
                    //自定义标题别名
                    writer.setHeaderAlias(getOtherSheetHeaderName());
                    Map<String, List<InformationDataColumnExcelExVo>> groupByList = clomnList.stream().collect(Collectors.groupingBy(InformationDataColumnExcelExVo::getDataSourceId));
                    String sourceId = directory.getId();
                    List<InformationDataColumnExcelExVo> informationDataColumnExcelDtos = groupByList.get(sourceId);
                    if (informationDataColumnExcelDtos != null) {
                        writer.write(informationDataColumnExcelDtos, true);
                    }
                }
            }
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            writer.flush(response.getOutputStream());
        } catch (Exception e) {
            log.error("【导出excel异常 :{}】", e.getMessage());
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }
    }

    /**
     * 组装目录层级名称
     * @param directoryList
     */
    public void builderLevelName(List<InformationDataTableExcelExVo> directoryList) {
        directoryList.stream().forEach(tableExcelVo -> {
            String parentId = tableExcelVo.getParentId();
            String level = tableExcelVo.getLevel();
            String tempLevel = tableExcelVo.getTempLevel();
            tableExcelVo.setOneLevel(tempLevel);
            if ("2".equals(level)) {
                //当前目录是二级
                tableExcelVo.setTwoLevel(tempLevel);
                InformationDataDirectoryEx directoryObj = getParentNameByPid(parentId);
                tableExcelVo.setOneLevel(directoryObj != null ? directoryObj.getName() : "");
            }
            if ("3".equals(level)) {
                //当前目录是三级
                tableExcelVo.setThreeLevel(tempLevel);
                InformationDataDirectoryEx directoryObj = getParentNameByPid(parentId);
                tableExcelVo.setTwoLevel(directoryObj != null ? directoryObj.getName() : "");
                InformationDataDirectoryEx oneLevelObj = getParentNameByPid(directoryObj.getParentId());
                tableExcelVo.setOneLevel(oneLevelObj != null ? oneLevelObj.getName() : "");
            }
        });
    }


    /**
     * 根据表ids获取对应的字段数据
     * @param tableAndDirByDirIds
     * @return
     */
    public  List<InformationDataColumnExcelExVo> getExcelDataColumnExcelDtoList(List<InformationDataTableExcelExVo> tableAndDirByDirIds) {
        // 按每1000个一组分割
        int partialLimit = 1000;
        Set<String> tableIds = tableAndDirByDirIds.stream().map(InformationDataTableExcelExVo::getId).collect(Collectors.toSet());
        // 获取需要分割的次数，注意不能直接除以批次数量
        int limit = (tableIds.size()+partialLimit -1)/partialLimit;
        List<Set<String>> mglist = new ArrayList<>();
        Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {
            mglist.add(tableIds.stream().skip(i * partialLimit).limit(partialLimit).collect(Collectors.toSet()));
        });
        List<Object[]> allClomnObjList = new ArrayList<>();
        for (int i = 0; i < mglist.size(); i++) {
            Set<String> strings = mglist.get(i);
            List<Object[]> clomnObjList = informationDataColumnRepository.getClomnByTableIds(strings);
            allClomnObjList.addAll(clomnObjList);
        }
        return allClomnObjList.stream().map(item -> {
            InformationDataColumnExcelExVo dto = new InformationDataColumnExcelExVo();
            dto.setTableName(String.valueOf(item[0]));
            dto.setId(String.valueOf(item[1]));
            dto.setEnName(String.valueOf(item[2]));
            dto.setChName(String.valueOf(item[3]));
            dto.setColType(String.valueOf(item[4]));
            String isKey = String.valueOf(item[5]);
            if ("1".equals(isKey)){
                dto.setIsKey("是");
            }else if("0".equals(isKey)){
                dto.setIsKey("否");
            }
            String isNull = String.valueOf(item[6]);
            if ("1".equals(isNull)){
                dto.setIsNull("是");
            }else if("0".equals(isNull)){
                dto.setIsNull("否");
            }
            Clob clob = (Clob) item[7];
            if (clob != null) {
                String strRemark = clobToString(clob);
                dto.setRemark(strRemark);
            }
            dto.setTableId(String.valueOf(item[8]));
            dto.setDataSourceId(String.valueOf(item[9]));
            return dto;
        }).collect(Collectors.toList());
    }


    /**
     * @Description: 将clob类型转化为String类型
     */
    public String clobToString(Clob clob) {
        String clobToString = "";
        if(null == clob){
            return clobToString;
        }
        try{
            Reader is = clob.getCharacterStream();// 得到流
            BufferedReader br = new BufferedReader(is);
            String s = br.readLine();
            StringBuffer sb = new StringBuffer();
            while (s != null) {
                // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
                sb.append(s);
                s = br.readLine();
            }
            clobToString = sb.toString();
        }catch (Exception e){
            e.getStackTrace();
        }
        return clobToString;
    }


    private void exportExcel(List<InformationDataTableExcelExVo> tableList, HttpServletResponse response, List<InformationDataColumnExcelExVo> clomnList) {
        ExcelWriter writer = new ExcelWriter(true,"目录");
        //自定义标题别名
        Map<String, String> headerAliasP = getOneSheetHeaderName();
        writer.setHeaderAlias(headerAliasP);
        writer.setOnlyAlias(true);
        writer.write(tableList,true);
        //设置所有列为自动宽度，不考虑合并单元格
        writer.autoSizeColumnAll();
        writer.setSheet("字段信息");
        //自定义标题别名
        Map<String, String> headerAliasE = getOtherSheetHeaderName();
        writer.setHeaderAlias(headerAliasE);
        // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
        writer.write(clomnList,true);
        //setSizeColumn(writer.getSheet(),1);
        //设置自动宽度，不考虑合并单元格
        writer.autoSizeColumnAll();
        try {
            InformationDataTableExcelExVo informationDataTableExcelVo = tableList.get(0);
            String chName = informationDataTableExcelVo.getChName();
            if (StringUtils.isBlank(chName)){
                chName = informationDataTableExcelVo.getEnName();
            }
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            String  codedFileName = URLEncoder.encode(chName, "UTF-8");
            response.setHeader("Content-Disposition","attachment;filename="+ codedFileName + ".xls");
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            writer.flush(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
            log.error("【导出excel异常 :{}】",e.getMessage());
        } finally {
            // 关闭writer，释放内存
            writer.close();
        }
    }

    private Map<String, String> getOtherSheetHeaderName() {
        Map<String, String> headerAliasE = new LinkedHashMap<>();
        headerAliasE.put("tableName", "表名");
        headerAliasE.put("enName", "列名");
        headerAliasE.put("chName", "中文名称");
        headerAliasE.put("colType", "类型");
        headerAliasE.put("isNull","空否");
        headerAliasE.put("isKey","是否为主键");
        headerAliasE.put("remark","备注");
        return headerAliasE;
    }

    private Map<String, String> getOneSheetHeaderName() {
        Map<String, String> headerAliasP = new LinkedHashMap<>();
        headerAliasP.put("dataSourceName", "库名");
        headerAliasP.put("enName", "库表英文名");
        headerAliasP.put("chName", "库表中文名");
        headerAliasP.put("oneLevel", "厂商维度一级");
        headerAliasP.put("twoLevel", "厂商维度二级");
        headerAliasP.put("threeLevel", "厂商维度三级");
        headerAliasP.put("frequency", "采集频率");
        headerAliasP.put("content", "说明");
        headerAliasP.put("purchasStatus", "采购状态");
        return headerAliasP;
    }

    /**
     * 根据表的id获取字段信息
     *
     * @param id id 表id
     * @return
     */
    private List<InformationDataColumnExcelExVo> getClomnList(String id) {
        QInformationDataTableEx qTable = QInformationDataTableEx.informationDataTableEx;
        QInformationDataColumnEx qClo = QInformationDataColumnEx.informationDataColumnEx;
        Predicate predicate = qTable.isNotNull().or(qTable.isNull());
        predicate = ExpressionUtils.and(predicate, qClo.tableId.eq(id));
        List<InformationDataColumnExcelExVo> columnExcelVoList = jpaQueryFactory.select(buildQueryBean(qClo, qTable))
                .from(qClo)
                .leftJoin(qTable).on(qTable.id.eq(qClo.tableId))
                .where(predicate)
                .fetch();
        columnExcelVoList.stream().forEach(columnExcelVo -> {
            columnExcelVo.setIsKey("1".equals(columnExcelVo.getIsKey()) ? "是" : "否");
            columnExcelVo.setIsNull("1".equals(columnExcelVo.getIsNull()) ? "是" : "否");
        });
        return columnExcelVoList;
    }

    /**
     * 根据表id获取数据表信息 和目录层级、数据源名称
     *
     * @param
     * @return
     */
    private List<InformationDataTableExcelExVo> getTableList(InformationDataTableEx tableObj) {
        //InformationDataTableEx tableObj = informationDataTableRepository.getOne(id);
        if (ObjectUtil.isEmpty(tableObj)) {
            return new ArrayList<>();
        }
        QInformationDataTableEx qTable = QInformationDataTableEx.informationDataTableEx;
        QInformationDataDirectoryEx qInfoDir = new QInformationDataDirectoryEx("qInfoDir");
        QInformationDataDirectoryEx qInfoSourceDir = new QInformationDataDirectoryEx("qInfoSourceDir");
        Predicate predicate = qTable.id.eq(tableObj.getId());
        predicate = ExpressionUtils.and(predicate, qInfoDir.id.eq(tableObj.getDirId()).and(qInfoDir.type.eq(2)));
        predicate = ExpressionUtils.and(predicate, qInfoSourceDir.id.eq(tableObj.getDataSourceId()).and(qInfoSourceDir.type.eq(1)));
        List<InformationDataTableExcelExVo> tableExcelVoList = jpaQueryFactory.select(buildQueryBean(qTable, qInfoDir, qInfoSourceDir))
                .from(qTable)
                .leftJoin(qInfoDir).on(qTable.dirId.eq(qInfoDir.id))
                .leftJoin(qInfoSourceDir).on(qTable.dataSourceId.eq(qInfoSourceDir.id))
                .where(predicate)
                .fetch();
        InformationDataTableExcelExVo tableExcelVo = tableExcelVoList.get(0);
        String parentId = tableExcelVo.getParentId();
        String level = tableExcelVo.getLevel();
        String tempLevel = tableExcelVo.getTempLevel();
        tableExcelVo.setOneLevel(tempLevel);
        if ("2".equals(level)) {
            //当前目录是二级
            tableExcelVo.setTwoLevel(tempLevel);
            InformationDataDirectoryEx directoryObj = getParentNameByPid(parentId);
            tableExcelVo.setOneLevel(directoryObj != null ? directoryObj.getName() : "");
        }
        if ("3".equals(level)) {
            //当前目录是三级
            tableExcelVo.setThreeLevel(tempLevel);
            InformationDataDirectoryEx directoryObj = getParentNameByPid(parentId);
            tableExcelVo.setTwoLevel(directoryObj != null ? directoryObj.getName() : "");
            InformationDataDirectoryEx oneLevelObj = getParentNameByPid(directoryObj.getParentId());
            tableExcelVo.setOneLevel(oneLevelObj != null ? oneLevelObj.getName() : "");
        }
        return tableExcelVoList;
    }

    /**
     * 根据当前节点的父id 获取上级节点的名称
     *
     * @param parentId
     * @return
     */
    private InformationDataDirectoryEx getParentNameByPid(String parentId) {
        QInformationDataDirectoryEx directory = QInformationDataDirectoryEx.informationDataDirectoryEx;
        InformationDataDirectoryEx directoryObj = jpaQueryFactory.selectFrom(directory)
                .where(directory.id.eq(parentId))
                .fetchOne();
        return directoryObj;
    }

    /**
     * 组装sql查询的参数
     *
     * @param qClo   qClo
     * @param qTable qTable
     * @return QBean
     */
    private QBean<InformationDataColumnExcelExVo> buildQueryBean(QInformationDataColumnEx qClo, QInformationDataTableEx qTable) {
        //表名	列名	中文名称	类型	空否	是否为主键	备注
        return Projections.bean(InformationDataColumnExcelExVo.class,
                qTable.enName.as("tableName"),
                qClo.id,
                qClo.tableId,
                qClo.enName,
                qClo.chName,
                qClo.colType,
                qClo.isNull,
                qClo.isKey,
                qClo.remark
        );
    }

    /**
     * 组装sql查询的参数
     *
     * @param qTable     qTable
     * @param qInfoDir   qInfoDir
     * @param qSourceDir qSourceDir
     * @return
     */
    private QBean<InformationDataTableExcelExVo> buildQueryBean(QInformationDataTableEx qTable, QInformationDataDirectoryEx qInfoDir, QInformationDataDirectoryEx qSourceDir) {
        //库名	库表英文名 库表中文名 厂商维度一级 厂商维度二级	厂商维度三级 层级 是否子节点	采集频率	说明
        return Projections.bean(InformationDataTableExcelExVo.class,
                qSourceDir.name.as("dataSourceName"),
                qTable.id,
                qTable.dirId,
                qTable.dataSourceId,
                qTable.enName,
                qTable.chName,
                qInfoDir.name.as("tempLevel"),//临时目录
                qInfoDir.level,
                qInfoDir.isLeaf,
                qInfoDir.parentId,
                qTable.frequency,
                qTable.content,
                qTable.purchasStatus
        );
    }


    /**
     * 自适应宽度(中文支持)
     *
     * @param sheet
     * @param size  因为for循环从0开始，size值为 列数-1
     */
    public static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    @Override
    public Map moveTable(String id, String tagId, String moveType) {
        Map<String, Object> map = new HashMap<>();
        ArrayList<InformationDataTableEx> list = new ArrayList<>();
        map.put("flag",false);
        //校验目标是否存在
        InformationDataTableEx table = informationDataTableRepository.getOne(id);
        if (null == table){
            map.put("message","要移动的数据表不存在!");
            return map;
        }
        QInformationDataTableEx qInformationDataTable = QInformationDataTableEx.informationDataTableEx;
        //判断是移到目录里还是表旁边
        if("inner".equals(moveType)){ //移到目录里
            InformationDataDirectoryEx tagDir = informationDataDirRepository.getOne(tagId);
            if ("0".equals(tagDir.getIsLeaf())){
                map.put("message","不允许移动到非叶子目录下！");
                return map;
            }
            List<InformationDataTableEx> informationDataTables = jpaQueryFactory.selectFrom(qInformationDataTable)
                    .where(qInformationDataTable.dirId.eq(tagId))
                    .fetch();
            if (CollectionUtils.isNotEmpty(informationDataTables)){
                map.put("message","当前目录下存在其他表，请选择具体位置!");
                return map;
            }
            table.setDirId(tagId);
            list.add(table);
        }else { //移动到表旁边
            InformationDataTableEx tagTable = informationDataTableRepository.getOne(tagId);
            if (null == tagTable){
                map.put("message","要移动到的位置不存在!");
                return map;
            }
            //不允许跨数据源移动
            if (!table.getDataSourceId().equals(tagTable.getDataSourceId())){
                map.put("message","不允许跨数据源移动!");
                return map;
            }
            //不允许移动到非叶子节点
            InformationDataDirectoryEx tagDir = informationDataDirRepository.getOne(tagTable.getDirId());
            if (!"1".equals(tagDir.getIsLeaf())){
                map.put("message","不允许移动到非叶子目录!");
                return map;
            }
            //获取兄弟节点
            List<InformationDataTableEx> brotherList = jpaQueryFactory.selectFrom(qInformationDataTable)
                    .where(qInformationDataTable.dirId.eq(tagTable.getDirId()))
                    .orderBy(qInformationDataTable.sort.asc(), qInformationDataTable.updateTime.desc(), qInformationDataTable.enName.asc())
                    .fetch();
            //获取位置
            int tag = 0;
            for (int i = 0; i < brotherList.size(); i++) {
                //找到 目标位置
                String id1 = brotherList.get(i).getId();
                if (id1.equals(tagId)) {
                    tag = i;
                }
            }
            //判断位置是否是底部位置
            boolean flag = false;
            if ("after".equals(moveType) && (tag + 1) == brotherList.size()) {
                flag = true;
            }
            if (flag) {
                //获取前临的兄弟的排序号，加一
                InformationDataTableEx beforeBrother = brotherList.get(tag);
                table.setSort(beforeBrother.getSort() + 1);
                table.setDirId(beforeBrother.getDirId());
                list.add(table);
            } else {
                //处理本身
                //获取前临的兄弟的排序号
                InformationDataTableEx beforeBrother = brotherList.get(tag);
                table.setDirId(tagTable.getDirId());
                int begin = 0;
                int sort = 0;
                if ("after".equals(moveType)) { //之后加1
                    table.setSort(tagTable.getSort() + 1);
                    sort = tagTable.getSort() + 1;
                    begin = tag + 1;
                } else if ("before".equals(moveType)) { //之前 直接替换兄弟的排序号
                    table.setSort(tagTable.getSort());
                    sort = tagTable.getSort();
                    begin = tag;
                }
                for (int i = begin; i < brotherList.size(); i++) {
                    InformationDataTableEx brother = brotherList.get(i);
                    if (!brother.getId().equals(id)) {
                        sort += 1;
                        brother.setSort(sort);
                        list.add(brother);
                    }
                }
                list.add(table);
            }
        }
        try {
            informationDataTableRepository.saveAll(list);
            map.put("flag", true);
            map.put("message", "移动表成功！");
        } catch (Exception e) {
            e.printStackTrace();
            map.put("flag", false);
            map.put("message", "移动表失败！");
        }
        return map;
    }
}
